/* 
 * Author: Alexander Rauser, arauser.org
 * Date: August 2008
 * Copyright: 2008
 * Licensed under Creative Commons Attribution 3.0 Unported
 */
 
package org.arauser.uploader.model
{
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.filesystem.File;
	import flash.utils.Dictionary;
	
	import mx.controls.Alert;
	
	import org.puremvc.as3.interfaces.IProxy;
	import org.puremvc.as3.patterns.proxy.Proxy;
    
    /**
    * Proxy that loads and saves settings in the database.
    */
	public class SettingsProxy extends Proxy implements IProxy
	{ 
      	public static const NAME:String = 'SettingsProxy';
        private var conn:SQLConnection;
        private var _settings:Dictionary;
        
        /**
        * Get a new or cached version of the current settings.
        */
        public function get settings():Dictionary
        {
        	if(_settings == null)
        	{
      			loadSettings();
        	}
      		return _settings;
        }
		
		public function SettingsProxy()
		{
			super( NAME );
          	
          	conn = new SQLConnection();
          	
          	createDatabase();
		}
		
		/**
		 * Creates the database if it doesn't already exist.
		 */
		private function createDatabase():void
		{
			var dbFile:File = File.applicationStorageDirectory.resolvePath("uploader.db");
	
			try
			{
			    conn.open(dbFile);
			    createTable();
			}
			catch (error:SQLError)
			{
				mx.controls.Alert.show("Details:", error.details);			    
			}
		}
		
		/**
		 * Creates the table for the settings if not exists.
		 */
		private function createTable():void
		{
			var createStmt:SQLStatement = new SQLStatement();
			createStmt.sqlConnection = conn;
			
			var sql:String = 
			    "CREATE TABLE IF NOT EXISTS settings (" + 
			    "    settingId INTEGER PRIMARY KEY AUTOINCREMENT, " + 
			    "    settingName TEXT, " + 
			    "    settingValue TEXT " + 
			    ")";
			createStmt.text = sql;

			try
			{
			    createStmt.execute();
			}
			catch (error:SQLError)
			{
			    mx.controls.Alert.show("Details:", error.details);
			}
		}
		
		
		/**
		 * Load all settings and stores them in the settings Dictionary.
		 */
		public function loadSettings():void
		{
			_settings = new Dictionary();
			
			var selectStmt:SQLStatement = new SQLStatement();
			selectStmt.sqlConnection = conn;
			
			selectStmt.text = "SELECT * FROM settings";

			try
			{
			    selectStmt.execute();
			    
			    var result:SQLResult = selectStmt.getResult();
			    if(result.data != null)
			    {
			    	for each(var item:Object in result.data)
			    	{
			    		_settings[item.settingName] = item.settingValue;
			    	}
			    }
			}
			catch (error:SQLError)
			{
			    mx.controls.Alert.show("Details:", error.details);
			}
		}
				
		/**
		 * Loads a single setting value by name.
		 */
		public function loadSetting(name:String):String
		{
			var value:String;
			var selectStmt:SQLStatement = new SQLStatement();
			selectStmt.sqlConnection = conn;
			
			selectStmt.text = "SELECT * FROM settings WHERE settingName = '" + name + "'";

			try
			{
			    selectStmt.execute();
			    
			    var result:SQLResult = selectStmt.getResult();
			    if(result.data != null)
			    {
					value = result.data[0].settingValue;
			    }
			}
			catch (error:SQLError)
			{
			    mx.controls.Alert.show("Details:", error.details);
			}
			return value; 
		}
		
		/**
		 * Saves a setting value by given name.
		 * @param name The name of the setting
		 * @param value The value of the setting to be saved
		 */
		public function saveSetting(name:String, value:String):void
		{
			_settings[name] = value;
			
			var saveStmt:SQLStatement = new SQLStatement();
			saveStmt.sqlConnection = conn;
			
			saveStmt.text = "SELECT * FROM settings WHERE settingName = '" + name + "'";

			try
			{
			    saveStmt.execute();
			    var result:SQLResult = saveStmt.getResult();
				
				if(result.data != null && result.data.length > 0)
				{
					saveStmt.text = "UPDATE settings SET settingValue = '"+value+"' WHERE settingName='"+name+"'";
				}
				else
				{
					saveStmt.text = "INSERT INTO settings (settingName, settingValue) values ('"+name+"','"+value+"')";
				}
				saveStmt.execute();
				
			}
			catch (error:SQLError)
			{
				
			    mx.controls.Alert.show("error"+error.details);
			}
		}		
		


	}
}